﻿CREATE PROCEDURE [dbo].[sp_Get_OrderInfo]
	@Month int,
	@Year int
AS
	select os.Division, os.Market, a.FirstName + ' ' + a.LastName, os.ActiveCount, 
	os.LapsedCount, os.NeighborCount, os.BusinessCount, os.UpdateTS, 
	case when dm.AgentCount is not null then dm.AgentCount else a.AgentNumber end from 
	(select zi.Division, zi.Market, o.AccountID, MAX(o.UpdateTS) as UpdateTS, 
	sum(case when oz.Type = 2 then 0 else zi.ActiveStopCount end) as ActiveCount,
	sum(case when oz.Type = 2 then 0 else zi.LapsedStopCount end) as LapsedCount, 
	sum(case when oz.Type = 2 then 0 else zi.NeighborLeadCount end) as NeighborCount,
	sum(case when oz.Type = 1 then 0 else zi.BusinesLeadCount end) as BusinessCount
	from dbo.Orders o
	inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
	and o.PeroidYear =@Year and o.PeroidMonth =@Month and o.IsPurchased = 1
	inner join dbo.ZipInfo zi on zi.ZipID = oz.ZipID
	group by o.OrderID, o.AccountID, zi.Division, zi.Market)os
	inner join dbo.Accounts a on os.AccountID = a.AccountID and a.Status <> 2
	left join dbo.DivisionMarket dm on os.AccountID = dm.MdNumber and os.Division = dm.Division and os.Market = dm.Market
	order by os.AccountID